/**
 * PictureDaoImp.java
 *
 *
 * $LastChangedBy: allen $
 * $LastChangedDate: 2007-12-11 17:22:48 +0800 $
 * $Revision: 1440 $
 */
package com.myspace.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.myspace.dao.PictureDao;
import com.myspace.domain.Picture;
import com.myspace.domain.User;

/**
 * Dao implements for picture.
 *
 * @author Allen.Chen
 *
 */
public class PictureDaoImp extends GenericDaoImp<Picture, Long> implements
		PictureDao {

	@SuppressWarnings("unchecked")
	public List<Picture> getPictureListByTag(final User user, final String tag,
			final int pageNum, final int pageSize) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

//				String sql = "select pic from Picture as pic" +
//								" left join pic.tags as tag " +
//								" where pic.user=:user and tag.name=:name " +
//								" order by pic.createDate";
//				Query query = session.createQuery(sql);
//				query.setEntity("user", user);
//				query.setString("name", tag);
//				query.setFirstResult((pageNum - 1) * pageSize);
//				query.setMaxResults(pageSize);
//				List<Picture> list = (List<Picture>) query.list();
//				System.out.println(list.get(0).getTags().get(0).getName());
//				return list;

				Criteria criteria = session.createCriteria(Picture.class);
				criteria.add(Restrictions.eq("user", user)).createCriteria(
						"tags").add(Restrictions.eq("name", tag));
				criteria.setFirstResult((pageNum - 1) * pageSize);
				criteria.setMaxResults(pageSize);
				criteria.addOrder(Order.desc("createDate"));
				List<Picture> list = (List<Picture>) criteria.list();
				return list;
			}
		});
	}

	@SuppressWarnings("unchecked")
	public List<Picture> getPictureListByDate(final User user,
			final Date fromDate, final Date endDate, final int pageNum,
			final int pageSize) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				String sql = "select t.id from Picture t "
						+ " where t.user_id =:userId "
						+ " and t.create_date between :fromDate and :endDate "
						+ " order by t.id desc";
				SQLQuery sqlQuery = session.createSQLQuery(sql).addScalar("id",
						Hibernate.LONG);
				sqlQuery.setLong("userId", user.getId());
				sqlQuery.setDate("fromDate", fromDate);
				sqlQuery.setDate("endDate", endDate);
				// sqlQuery.setString("fromDate", "to_date('"+
				// Formatter.formatYYYYMMDD(fromDate) + " 00:00:00','yyyy/mm/dd
				// HH24:mi:ss')");
				// sqlQuery.setString("endDate", "to_date('"+
				// Formatter.formatYYYYMMDD(endDate) + " 00:00:00','yyyy/mm/dd
				// HH24:mi:ss')");
				sqlQuery.setFirstResult((pageNum - 1) * pageSize);
				sqlQuery.setMaxResults(pageSize);
				List<Long> idList = sqlQuery.list();

				if(idList == null || idList.size() ==0)
					return null;

				Criteria criteria = session.createCriteria(Picture.class);
				/*
				 * NOTE: set Criteria.DISTINCT_ROOT_ENTITY is required, "left
				 * outer join" will result in repeated record in result set, for
				 * one picture own more than one tag.
				 */
				criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
				criteria.add(Restrictions.in("id", idList.toArray()));
				criteria.addOrder(Order.desc("id"));
				List<Picture> list = (List<Picture>) criteria.list();
				return list;

				// TODO: it will cause N+1 select, but "left join fetch" is
				// prohibited in sub-select sentence.
				// String sql = "from Picture v where v.id in " +
				// "(select distinct t.id from Picture as t left join t.tags
				// where t.user =:user)" +
				// "order by v.createDate desc";
				// Query query = session.createQuery(sql);
				// query.setEntity("user", user);
				// query.setFirstResult((pageNum - 1) * pageSize);
				// query.setMaxResults(pageSize);
				// List<Picture> list = query.list();

				// TODO: the result is not correct because of the seperated
				// page.
				// Criteria criteria = session.createCriteria(Picture.class);
				// criteria.add(Restrictions.between("createDate", fromDate,
				// endDate));
				// criteria.add(Restrictions.eq("user", user));
				// criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
				// criteria.addOrder(Order.desc("createDate"));
				// criteria.setFirstResult((pageNum - 1) *
				// pageSize).setMaxResults(pageSize);
				// List<Picture> list = (List<Picture>) criteria.list();
				// return list;
			}
		});
	}

	@SuppressWarnings("unchecked")
	public List<Picture> getPictureList(final User user) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(Picture.class);
				criteria.add(Restrictions.eq("user", user));
				List<Picture> list = (List<Picture>) criteria.list();
				return list;
			}
		});
	}

	public int getPictureNumByDate(final Long userId, final Date fromDate,
			final Date endDate) {
		Integer ret = (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String sql = "select count(t.id) as num from Picture t "
								+ " where t.user_id =:userId "
								+ " and t.create_date between :fromDate and :endDate";
						SQLQuery sqlQuery = session.createSQLQuery(sql)
								.addScalar("num", Hibernate.INTEGER);
						sqlQuery.setLong("userId", userId);
						sqlQuery.setDate("fromDate", fromDate);
						sqlQuery.setDate("endDate", endDate);
						return sqlQuery.uniqueResult();
					}
				});
		return ret == null ? 0 : ret.intValue();
	}

	public int getPictureNumByTag(final Long userId, final String tag) {
		Integer ret = (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String sql = "select count(t.id) as num "
								+ " from picture t, rel_tag_picture r, tag v "
								+ " where t.user_id =:userId and v.name =:tag "
								+ " and t.id = r.pic_id and v.id= r.tag_id";
						SQLQuery sqlQuery = session.createSQLQuery(sql)
								.addScalar("num", Hibernate.INTEGER);
						sqlQuery.setLong("userId", userId);
						sqlQuery.setString("tag", tag);
						return sqlQuery.uniqueResult();
					}
				});
		return ret == null ? 0 : ret.intValue();
	}



}
